<?

require_once '../include/common.inc.php';
//error_reporting(E_ALL);

if (!checkAdmin()) {
    header("LOCATION:index.php");
    exit;
}

$menu_index = 1;
$page_title = 'JANSEN';
$guide_message = 'Product | Basic Infomation | Import Data';

getMyParam('importtype');

$error_str = '';
$upload_file = "";

// read the excel from the file  ::start
if ($importtype == '1' || $importtype == '2')
 {		
    if(strtolower(getExt($_FILES['upload_file']['name'])) == "xls" && $_FILES['upload_file']['size'] > 0 && ($_FILES['upload_file']['type'] == "application/vnd.ms-excel" || $_FILES['upload_file']['type'] == "application/octet-stream")){
        $upload_file = $_FILES['upload_file']['tmp_name'];

        // ===============Get excel start=======================
        $user_method = 1;
       
	   //1:methords (reader)
	    require_once '../include/reader.php';
        $data = new Spreadsheet_Excel_Reader();
        //$data->setOutputEncoding('CP1251');
       // $data->setOutputEncoding('gb2312');
		//$data->setOutputEncoding('utf-8');
		$data->setOutputEncoding('iso-8859-1');
        $data->read($upload_file);
        $tmp_ary = $data->sheets[0]['cells'];
     
		if (is_array($tmp_ary) || $tmp_ary[1][1] == 'Web code nr')
		{
            foreach ($tmp_ary as $k1=>$v1)
			 {
                $tmp2_ary = array();
                foreach ($v1 as $k2=>$v2)
				{
                    $tmp2_ary[$k2-1] = $v2;					
                }
                $excel_ary[$k1-1] = $tmp2_ary;
            }
        } 
		else 
		{
            $excel_ary = '';
        }
        // 2: methords(execl.class.php)
		if (!is_array($excel_ary) || $excel_ary[0][0] != 'Web code nr') {
            $user_method = 2;
            require_once '../include/excel.class.php';
            Read_Excel_File($upload_file, $return);

            if (is_array($return)) {
                $index = 0;
                foreach ($return as $k=>$v) {
                    if ($index == 0) {
                        $excel_ary = $v;
                    }
                    $index++;
                }
            } else {
                $excel_ary = '';
            }
        }
        // 3:methords (PHPExcel)
		if (!is_array($excel_ary) || $excel_ary[0][0] != 'Web code nr') {
            $user_method = 3;
            $excel_ary = array();
            $excel_ary1 = file($upload_file);

            foreach ($excel_ary1 as $k=>$v) {
                $tmp_ary = explode("\t", $v);
                $excel_ary[$k] = $tmp_ary;
            }
        }
        //=============== get  excel end=======================
	
		// Checked first Columns  === Web code nr
		if (trim($excel_ary[0][0]) != 'Web code nr')
		{  
			$error_str .= "<div class='js_msg'>excel error in line: code</div>";
		}

    }
	elseif ($_FILES['upload_file']['size'] > 0) 
	{
        $error_str .= "<div class='js_msg'>excel file error</div>";
    }
}
// read the excel from the file  ::end



// ==================== insert into database ==============================
if ($importtype == '1' && $error_str =="") {
	


	//$product_hava_ary   
	// $this_fid
	//Finishes_Category  $aryFinishesID = array();
	//Setting	$arySettingID = array();
	//Style	$aryStyleID = array();
	//Collection	$aryCollectionID = array();
	
	//$excel_sql_ary Common.admin的数组
	//excel_ary  excel 的数组
	//$sql_product_ary  二维数组 临时存放产品的数组
	//$sql_category_ary  //索引表
	//$cat_col_ary  类别数组 ;
	// $product_Category_ary 产品类别数组

	//取得产品 CODE为索引的产品 ID列表
	//GET the product code array
    $product_hava_ary = array();
    $sql = "SELECT `id`, `code` FROM `product`";
    $query = $db->query($sql);
    while($rs = $db->fetch_array($query)) {
        $rs['code'] = trim(strtolower($rs['code']));
        $product_hava_ary[$rs['code']] = $rs['id'];
    }

  
	//取得类型值，从文件的名称获取. 取出类别ID号
	//get the file name from upload file ,and change to category ID; 
    $category_name = basename($_FILES['upload_file']['name'], ".xls");
	$category_name = str_replace('_',' ',trim($category_name));		
	
	$sql = "SELECT `id`,`name` FROM `category` WHERE `fid` = 0";
	$query = $db->query($sql);	
	while($rs=$db->fetch_array($query))
	{
	    if(preg_replace("/<[^>]+>/","",$rs["name"])==$category_name)
		{			
			$this_fid = $rs["id"];				
		}	
	}
	if (empty($this_fid)) {
        $error_str .= "<span class='js_msg'>Category error </span>";
    }
	
	
	
	
  
  $product_Category_ary = array();
  $product_Category_ary_arr = array();
    $sql = "SELECT `product_id`,`category_id`,`seq` FROM `product_category`";
    $query = $db->query($sql);
    while($rs = $db->fetch_array($query)) {        
        $product_Category_ary[] = $rs['product_id'];
		$product_Category_ary_arr[$rs['product_id']][]=$rs['category_id'];
    }
	
	
	
	//Add by Leo 2010-07-07:
	//GET sub category ID string whith fid == $thisid;
	$cat_col_ary = array(); 
	$aryIn = get_category(0);
	GetProductCateIDAryByID($aryIn,$this_fid,&$cat_col_ary);
	$str_sub_cateid = GetCategoryTreeIDS($aryIn,$this_fid,false);
	
	//echo $str_sub_cateid."<br/>";
	
	//add by martin
	$category_col_ary = array(); 
	newGetProductCateIDAryByID($aryIn,$this_fid,&$category_col_ary);
	
	//Finishes
	$aryFinishesID = array();
	$sql = "SELECT `id`, `name` FROM `p_finish_set`";
	$query = $db->query($sql);
	while($rs = $db->fetch_array($query)) {
		$aryFinishesID[strtolower($rs['name'])] = $rs["id"];
	}
	
	//Setting
	$arySettingID = array();
	$sql = "SELECT `id`, `name` FROM `p_setting`";
	$query = $db->query($sql);
	while($rs = $db->fetch_array($query)) {
		$arySettingID[strtolower($rs['name'])] = $rs["id"];
	}
	$arySettingID=array_unique($arySettingID);
	//Style
	$aryStyleID = array();
	$sql = "SELECT `id`, `name` FROM `p_style`";
	$query = $db->query($sql);
	while($rs = $db->fetch_array($query)) {
		$aryStyleID[strtolower($rs['name'])] = $rs["id"];
	}
	
	//Collection
	$aryCollectionID = array();
	$sql = "SELECT `id`, `name` FROM `p_collection`";
	$query = $db->query($sql);
	while($rs = $db->fetch_array($query)) {
		$aryCollectionID[strtolower($rs['name'])] = $rs["id"];
	}
	
	//category 	
	$Category_All_Arr = array();
	$Category_All_NameId=array();
	$sql = "SELECT `id`, `fid`,`code`,`name` FROM `category`";
	$query = $db->query($sql);
	while($rs = $db->fetch_array($query)) {
		$Category_All_Arr[$rs['id']] = $rs["fid"];
		$Category_All_NameId[$rs["name"]]=$rs['id'];
	}
	
	// fabric 
	$fabric_arr=array();
	$fabric_has_id=array();
	$sql = "SELECT `id`, `name`,`photo` FROM `p_fabric` ";
	$query = $db->query($sql);
	while($rs = $db->fetch_array($query))
	 {
		$fabric_arr[trim(strtolower($rs['name']))] = $rs['id'];	
		$fabric_has_id=$rs['id'];
		
	}
	
	// leather 
	$leather_arr=array();
	$leather_has_id=array();
	$sql = "SELECT `id`, `name`,`photo` FROM `p_leather` ";
	$query = $db->query($sql);
	while($rs = $db->fetch_array($query)) 
	{
		$leather_arr[trim(strtolower($rs['name']))] = $rs['id'];	
		$leather_has_id[]=$rs['id'];	
	}
	


    $category_index = 1;	
	
/*	foreach($category_col_ary as  $k=>$v)
	{
	  echo $k."<br/>";
	}*/
	//$excel_sql_ary Common.admin的数组
	//excel_ary  excel 的数组
	//$sql_product_ary  二维数组 临时存放产品的数组
	//$sql_category_ary
	//$cat_col_ary  类别数组 ;
	
	//ͨм martin
	//取第一行的值
	
	
	//开始填充数据
	//首行代码
    foreach ($excel_ary[0] as $k=>$v) {		
        $v = (string)$v;		
        if(preg_match("/(.+[^\s]+)[\s]+$/i", $v, $match_ary)){
            $v = $match_ary[1];
			echo $v."<br/>";
        }
		
		
	//echo $v."<br/>";
	 //	debug($excel_ary[0],$excel_sql_ary,$cat_col_ary,$excel_category_column);
           
        if ($k>0 && trim($v) != "") {
            if (!array_key_exists($v, $excel_sql_ary) && !array_key_exists($v, $cat_col_ary) && !array_key_exists($v, $excel_category_column)) {
                $error_str .= "<div class='js_msg'>excel error in line: " . $v . "</div>";
			
            } elseif (array_key_exists($v, $excel_sql_ary)) {
				//Excel ȡһУȡӦֵֶ
				//把值赋值给$sql_product_ary    (索引$k 为$excel_sql_ary 的值)
				//echo $excel_sql_ary[$v].$k."<br>";
                $sql_product_ary[$excel_sql_ary[$v]] = array('sql_col'=>$excel_sql_ary[$v], 'col_num'=>$k);
				//echo $excel_sql_ary[$v].$sql_product_ary[$excel_sql_ary[$v]]['col_num']."<br>";
				//echo $excel_sql_ary[$v].$k."<br/>";
            } 
			
			if (array_key_exists($v, $excel_category_column )) {
                $k = trim($k);			
                //$sql_category_ary[] = array('categoryid'=>$cat_col_ary[$v], 'col_num'=>$k);
				$sql_category_ary[$excel_category_column[$v]] = array('sql_col'=>$excel_category_column[$v], 'col_num'=>$k);
				
            }
        }
    }	
	//首行代码结束	
 
//debug($excel_ary);
    /*
    echo "<pre>";
    echo $error_str;   // Ŀ

    //print_r($product_hava_ary);   // вƷcode
    //print_r($excel_sql_ary);
    //print_r($cat_col_ary);


    //print_r($sql_product_ary);
    //print_r($sql_category_ary);

    print_r($excel_ary);
    exit;
    */

    // ==================== ж end ==============================

	//	$excel_sql_ary 
    
	//if have data
    if (empty($excel_ary[1])) 
	{
        $error_str .= "<span class='js_msg'>excel error</span>";
    }

//填充到数据库
    if ($error_str == "") 
	{
	    //ready insert database product code array
        $code_ary = array();		
        $pre_product_id = 0;	
		//$cur_key 当前行
		$cur_key = 0;
		$columns=array();
		$Category_ID=$this_fid;
		//循环整个EXCEL表		
		$codePRE="";
		
		foreach ($excel_ary as $k=>$v)
		{
								
			// add by martin #################################insert Category where code empty			
			//$codePRE="";
			/*if($k>0&&isset($v[0])&&trim($v[0]) != "")
			{	
			   $codePRE=$v[0];
			}
			else
			{
			  			 
			 	if(!empty($v[27]))
						{
						
						$strt =GetFid($Category_All_Arr,$Category_All_NameId[$v[27]]);	
						$subar=array();
						$subar=explode(",",$strt);					
						foreach($subar as $ky=>$vb)
						{
						
						 // checked  the product is has category
						 if($vb!="100000"&&!in_array($vb,$product_Category_ary_arr[$product_hava_ary[strtolower($codePRE)]]))
						   {		
						   	 	   						   						   
						    $sqlsub = "INSERT INTO `product_category` (`product_id`, `category_id`, `seq`) VALUES ('" . $product_hava_ary[strtolower($codePRE)] . "', '" . $vb . "', '$category_index')";							 				
                         
						   $db->query($sqlsub);
                           $category_index += 1;							
							
						   }
						
						}						 
						
						}					
						
						
			}*/
			//#################################insert product	##############
			
			
			if ($k>0 && (isset($v[0]) && trim($v[0]) != ""))//if code no empty!	
			{
			
                $code = (string)$v[0];
				$codePRE=$code;	//for the next rows category
				$columns["code"]= (string)$v[0];
				//echo $columns["code"]."<br/>";
				// checked and  insert to 	$code_ary	
				if (in_array(strtolower($code), $code_ary) && $code!="") 
				{ 
                    $error_str .= "<div class='js_msg'>Code " . $code . " (" . $v[1] . ") have set.</div>";
                } else	 {		
				
					//if has do insert/update mark here		
					$code_ary[] = strtolower($code);	
					
					      $columns["name"]=$v[1];
					      $columns["hidden_flag"]=(strtolower($v[2])=='y')?0:1;
					      $columns["new_flag"]=(strtolower($v[3])=='y')?1:0;
						  //$columns["comment"]= strtoupper(substr($v[4],0,1)).substr($v[4],1);		
						  $columns["comment"]=isset($v[4])?ucwords($v[4]):"";			 
						  $columns["pic"]= strstr(strtolower($v[5]), ".jpg") ? $v[5] : "";						  
						  $columns["small_pic"]= strstr(strtolower($v[6]), ".jpg") ? $v[6] : "";;
						  $columns["price_2"]=$v[7];
						  $columns["price_1"]=$v[8];
						  $columns["price_3"]=$v[9];
						  $columns["length"]=$v[10];
						  $columns["width"]=$v[11];
						  $columns["height"]=$v[12];
						  $columns["cbm"]=$v[13];	
						  $columns["width2"]=$v[14];
						  $columns["length2"]=$v[15];
						  $columns["height2"]=$v[16];					
						  $columns["cuft"]=$v[17];	
									  		  
						  $columns["finishshown"]=isset($v[18])?$v[18]:"";
						  
						  $columns["fabricshown"]=isset($v[19])?$v[19]:"";
						  $columns["leathershown"]=isset($v[20])?$v[20]:"";
						  
						  $columns["marbleshown"]=isset($v[21])?$v[21]:"";
						  $columns["finish_id"]=isset($v[22])?$v[22]:"";
						  $columns["setting_id"]=isset($v[23])?$v[23]:"";
						 
						  $columns["style_id"]=$v[24];
						  $columns["collection_id"]=$v[25];						 
						  $columns["info"]=htmlentities($v[26],ENT_QUOTES,"UTF-8");
						  $columns["sub_id"]=$v[27];
						
                       //small pic
                        $v[6] = strstr(strtolower($v[6]), ".jpg") ? $v[6] : "";
                        if (!empty($v[6]) && !strpos($v[6], 's-') && $v[5] == $v[6] )
						 {
                            if (strpos($v[6], "-s.")) 
							{
                                $v[6] = str_replace("-s.", ".", $v[6]);
                            }
                            $v[6] = "s-" . $v[6];
                        }
                        $columns["small_pic"]= $v[6];
             										
					  
					   //name first letter to upper	
					     $string_tmp = htmlentities($v[1]);	
                        if (strstr($string_tmp, '&iexcl;&macr;')) {
                            $string_tmp = @str_replace('&iexcl;&macr;', '\'', $string_tmp);
                            $string_tmp = @str_replace('&iexcl;&reg;', '\'', $string_tmp);
                        }
                        if (strstr($string_tmp, '&iexcl;&deg;')) {
                            $string_tmp = @str_replace('&iexcl;&deg;', '"', $string_tmp);
                            $string_tmp = @str_replace('&iexcl;&plusmn;', '"', $string_tmp);
                        }
						$string_tmp = html_entity_decode($string_tmp);
						
						if(substr($string_tmp,0,1) == '"') $string_tmp = substr($string_tmp,1);
						if(substr($string_tmp,count($string_tmp)-1) == '"') $string_tmp = substr($string_tmp,0,count($string_tmp)-1);
						$string_tmp = strtoupper(substr($string_tmp,0,1)).substr($string_tmp,1);					
                        $v["name"] = $string_tmp;
						
						
						
				
						//Finishes
						if( $columns['finish_id']!="")
						{
						
							$columns['finish_id'] = strtolower($columns['finish_id']);							
							if(!array_key_exists($columns['finish_id'],$aryFinishesID))
							{
								$sql4 = "INSERT INTO `p_finish_set` ( `name` , `createtime` ) VALUES ('".$columns['finish_id']."', '".date("Y-m-d H:i:s")."');";
								$db->query($sql4);
								$columns['finish_id'] = $aryFinishesID[$columns['finish_id']] = $db->insert_id();
							}
							else
							{
								$columns['finish_id']= $aryFinishesID[$columns['finish_id']];
							}
						}					
						
		
						
					 //fabric
					   $fabric_id=0;
					   $columns['fabricshown'] = strtolower(trim($columns['fabricshown']));
						if($columns['fabricshown']!="")
						{							
							if(!array_key_exists($columns['fabricshown'],$fabric_arr) )
							{
								$sql4 = "INSERT INTO `p_fabric` (`name`,`namezh`,`creatdate`) VALUES('".$columns['fabricshown']."','".$columns['fabricshownzh']."','".time()."');";
								$db->query($sql4);
								$fabric_id = $fabric_arr[$columns['fabricshown']] = $db->insert_id();
							}
							else
							{
								$fabric_id= $fabric_arr[$columns['fabricshown']] ;
							}
						}
					
					// echo $columns["code"]."_ fabric :".$columns['fabricshown']."_".$fabric_id." leather :";
				
						 //leather
					   $leather_id=0;
					   $columns['leathershown'] = strtolower(trim($columns['leathershown']));
						if($columns['leathershown']!="")
						{
						if(!array_key_exists($columns['leathershown'],$leather_arr))
							{
								$sql4 = "INSERT INTO `p_leather` (`name`,`namezh`,`creatdate`) VALUES('".$columns['leathershown']."','".$columns['leathershownzh']."','".time()."');";
								$db->query($sql4);
								$leather_id = $leather_arr[$columns['leathershown']] = $db->insert_id();
							}
							else
							{
								$leather_id= $leather_arr[$columns['leathershown']] ;
							}							
						}
			     // echo $columns['leathershown']."_".$leather_id."<br/>";;
						
						
						
						
						//Style
						if($columns['style_id']!="")
						{
							$columns['style_id'] = strtolower($columns['style_id']);
							if(!array_key_exists(strtolower($columns['style_id']),$aryStyleID) && $columns['style_id']!="")
							{
								$sql4 = "INSERT INTO `p_style` (`name`,`namezh`,`createtime`) VALUES('".$columns['style_id']."','".$columns['style_idzh']."','".date("Y-m-d H:i:s")."');";
								$db->query($sql4);
								$columns['style_id'] = $aryStyleID[$columns['style_id']] = $db->insert_id();
							}
							else
							{
								$columns['style_id']= $aryStyleID[$columns['style_id']];
							}
						}
						
						
						//Collection
				       		if($columns['collection_id']!="")
						{
							$columns['collection_id'] = strtolower($columns['collection_id']);
							if(!array_key_exists(strtolower($columns['collection_id']),$aryCollectionID) && $columns['collection_id']!="")
							{
								$sql4 = "INSERT INTO `p_setting` (`name`,`namezh`,`createtime`) VALUES('".$columns['collection_id']."','".$columns['collection_idzh']."','".date("Y-m-d H:i:s")."');";
								$db->query($sql4);
								$columns['collection_id'] = $aryCollectionID[$columns['collection_id']] = $db->insert_id();
							}
							else
							{
								$columns['collection_id'] = $aryCollectionID[$columns['collection_id']];
							}	
						}										
						
							
				
				
						
					   //product 	insert or update
                        if (array_key_exists(trim(strtolower($code)), $product_hava_ary))
						 {					    
                            $update_sql_ary = array();							
							foreach($excel_sql_ary_Insert as $k00=>$vin)
							{
							   
							   $update_sql_ary[] = "`" . $vin . "` = '" . addslashes($columns[$vin]) . "'";			
							
							}				
                            $lt='';
							if($leather_id!=0)	
							$lt='`leather_id`='.$leather_id.', ';
							
							$ft='';
							if($fabric_id!=0)
							$ft='`fabric_id`='.$fabric_id.', ';	
							
                            $sql = "UPDATE `product` SET " .$lt.$ft. implode(",", $update_sql_ary) . ", `delete_flag`="."'0'".", `cate_seq`=$k WHERE `code` = '" . $code . "'";											
                           	$db->query($sql);
							
							
                        } else 
						 {

                            
                            $sql_ary_v = $sql_ary_value = array();
							
							foreach($excel_sql_ary_Insert as $ki=>$vi)
							{
							    $sql_ary_v[] = "`" . $vi . "`";
								$sql_ary_value[]= "'" . addslashes($columns[$vi]) . "'";
							
							}	
						 	
							$lt='';
							$lts='';
							if($leather_id!=0)	
							{
							  $lt='`leather_id`,';
							  $lts=$leather_id.",";
							}
							
							$ft='';
							$fts='';
							if($fabric_id!=0)
							{
							  $ft='`fabric_id`,';	
							  $fts=$fabric_id.",";
							}
							
							
                            $sql = "INSERT INTO `product` (`code`,`cate_seq`, ".$lt.$ft. implode(",", $sql_ary_v) . ") VALUES ('" . $code . "', " .$k.",".$lts.$fts.implode(",", $sql_ary_value) . ")";
                            $db->query($sql);
                            $product_hava_ary[strtolower($v[0])] = $db->insert_id();

                        }
                        
                        
						
                       //setting
						$SettingHas=array();
						 $columns['setting_id']=trim($columns['setting_id']);					
						if(isset($columns['setting_id'])&&$columns['setting_id']!='')
						{
						 
						
						  $arr=explode("|",$columns['setting_id']);
						  $arrzh=explode("|",$columns['setting_idzh']);
						
						  foreach($arr as $kk=>$vv)
						  {
						    	//print_r($columns['setting_id']);						   
						    $vv= htmlentities(strtolower(trim($vv)));
					        
						if(!array_key_exists($vv,$arySettingID) && $vv!="")
							{				
							    $vt =ucwords($vv);	
								$arySettingID[]=$vv;							
								$sql4 = "INSERT INTO `p_setting` (`name`,`namezh`,`createtime`) VALUES('".$vt."','".  $arrzh[$kk]."','".date("Y-m-d H:i:s")."');";							   
								$db->query($sql4);							
								$SettingHas[] = $db->insert_id();
								//$columns['setting_id'] = $arySettingID[$columns['setting_id']] = $db->insert_id();
							}
							else
							{
								//$columns['setting_id'] = $arySettingID[$columns['setting_id']];
								if(isset($arySettingID[$vv]))
								$SettingHas[] = $arySettingID[$vv];								
							}
						  
						  }
						  
						 
						
						}
						 if(isset($SettingHas)&&count($SettingHas)>0)
						  {
						     $sqld = "DELETE  FROM  `product_p_setting` WHERE `product_id`=".$product_hava_ary[strtolower($code)];							
							 $db->query($sqld);	
							 //print_r($SettingHas);
							// echo "<br/>";
							 //$SettingHas=array_unique($SettingHas);						
							foreach($SettingHas as $vss)
							{          
							   $sqld = "INSERT INTO `product_p_setting` (`setting_id`,`product_id`, `seq`) VALUES ('".$vss."','".$product_hava_ary[strtolower($code)]."','0')";	
							   //echo $sqld."<br/>";
							   $db->query($sqld);
							} 
						  
						  }
						
						
                      			
						//sub Category
						$columns["sub_id"] = trim($columns["sub_id"] ) == "" ? $this_fid : $category_col_ary[$columns["sub_id"]];						
						if(!empty($columns["sub_id"]))
						{
						
							$str =GetFid($Category_All_Arr,$columns["sub_id"]);								
							$subar=array();
							$subar=explode(",",$str);
							//echo count($subar)."<br/>";	
							foreach($subar as $ky=>$vb)
							{
							
							   if($vb!="100000"&&!in_array($vb,$product_Category_ary_arr[$product_hava_ary[strtolower($code)]]))
							   {		
																					   
								$sqlsub = "INSERT INTO `product_category` (`product_id`, `category_id`, `seq`) VALUES ('" . $product_hava_ary[strtolower($code)] . "', '" . $vb . "', '$category_index')";							 				
								$db->query($sqlsub);
								$category_index += 1;							
								
							   }
							
							}
						 
						
						}					
						
				
						
						// insert end						
						
                    				
						
				
                   
                }
				
				
            }
            /*
			else
			{
			//if code is empty
			if(!empty($v[27]))
						{
						
						$strt =GetFid($Category_All_Arr,$Category_All_NameId[$v[27]]);	
						$subar=array();
						$subar=explode(",",$strt);					
						foreach($subar as $ky=>$vb)
						{
						
						 // checked  the product is has category
						 if($vb!="100000"&&!in_array($vb,$product_Category_ary_arr[$product_hava_ary[strtolower($codePRE)]]))
						   {		
						   	 	   						   						   
						    $sqlsub = "INSERT INTO `product_category` (`product_id`, `category_id`, `seq`) VALUES ('" . $product_hava_ary[strtolower($codePRE)] . "', '" . $vb . "', '$category_index')";							 				
                         
						   $db->query($sqlsub);
                           $category_index += 1;							
							
						   }
						
						}						 
						
						}		
			}
			*/
			
        } //196: foreach ($excel_ary as $k=>$v)
		
		    
		
		$sql="select id,code from product where id in(select  product_id from `product_category` where category_id in (".$str_sub_cateid."))";
		$query = $db->query($sql);
		$product_in_category = array();
		while($rs = $db->fetch_array($query)) 
		{
			$product_in_category[$rs["code"]] = $rs["id"];
		}
		
		foreach($product_in_category as $product_code => $product_id)
		{
			if(!in_array(strtolower($product_code),$code_ary))
			{
				$sql3 = "update `product` set hidden_flag = 1 where `id` = $product_id";
				$db->query($sql3);
			}
		}
    }
	
	//193: if ($error_str == "")	
    // ================= end import excel ==========================
   

} elseif ($importtype == '2' && $error_str == "") {

    // ============================= import inventory data start==========================
    if ($excel_ary[0][1] != "Qty") {
        $error_str = "<div class='js_msg'>This isn't a inventory excel !</div>";
    } else {
        //if ($excel_ary[0][2] == "HKWHSP(USD)" && $excel_ary[0][3] == "HKWHSP(HKD)") {
		if ($excel_ary[0][2] == "Special Price" && $excel_ary[0][3] == "Special Price2") {
            foreach ($excel_ary as $k=>$v) {
                if ($k>=1 && isset($v[0])  && trim($v[0])!= '' && is_numeric($v[1]) && $v[1]>=0) {
                    if (!isset($v[2])) {
                        $v[2] = 0;
                    }
                    if (!isset($v[3])) {
                        $v[3] = 0;
                    }
                    $v[0] = strtolower($v[0]);
                    $id_ary[] = $v[0];
                    $code_ary[$v[0]]['code'] = $v[0];
                    $code_ary[$v[0]]['qty'] = $v[1];
                    $code_ary[$v[0]]['sp1'] = $v[2];
                    $code_ary[$v[0]]['sp2'] = $v[3];
					$code_ary[$v[0]]['inventory_name'] = trim($v[4]);
					$inventory_name_ary[] = trim($v[4]);
                }
            }

            //$inventory_name_ary = array(0=>"Indonesia Warehouse",1=>"Singapore Show Container");
            //echo "<pre>";print_r($code_ary);//exit;
            $id_ary = array_unique($id_ary);
            $inventory_name_ary = array_unique($inventory_name_ary);
            $inventory_name_str = implode("', '", $inventory_name_ary);
            $inventory_name_str = "'" . $inventory_name_str . "'";
            $sql = "SELECT `id`, `name` FROM `inventory`";
	
            $query = $db->query($sql);
            while($rs = $db->fetch_array($query)) {
                $inventory_name_id_ary[$rs['name']] = $rs['id'];
            }

            $id_code_ary = array();
            $have_set_ary = array();
            $code_tmp_ary = array();

            if (!empty($id_ary)) {
                $id_str = implode("', '", $id_ary);//code X
                $id_str = "'" . $id_str . "'";
                $sql = "SELECT `id`, `code` FROM `product` WHERE `code` IN (" . $id_str . ")";
                $query = $db->query($sql);
                while($rs = $db->fetch_array($query)) {
                    $id_ary2[] = $rs['id'];
                    $rs['code'] = strtolower($rs['code']);
                    $code_tmp_ary[] = $rs['code'];   //дڵcode飬жЩcodeڵ
                    $id_code_ary[$rs['id']] = $code_ary[$rs['code']];
                    //$id_code_ary[$rs['id']]['inventory_id'] = $inventory_name_id_ary[$id_code_ary[$rs['id']]['inventory_name']];
                    $id_code_ary[$rs['id']]['inventory_id'] = $inventory_name_id_ary[$code_ary[$rs['code']]['inventory_name']];
                }

                // жЩcode
                $no_code_ary = '';
                foreach ($id_ary as $k=>$v) {
                    if(!in_array($v, $code_tmp_ary)) {
                        $error_str .= $v . "<br>\r\n";
                    }
                }
                if (!empty($error_str)) {
                    $error_str = "<div class='js_msg'>code not exist:</div><br><br>" . $error_str;
                }

                if (!empty($id_ary2)) {
                    // жЩƷĳinventoryqtyѴ
                    $sql = "SELECT `product_id`, `inventory_id` FROM `product_inventory` WHERE `product_id` IN (" . implode(",", $id_ary2) . ")";

                    $query = $db->query($sql);
                    while($rs = $db->fetch_array($query)) {
                        $have_set_ary[] = $rs['product_id'] . "_" . $rs['inventory_id'];
                    }
                    $have_set_ary = array_unique($have_set_ary);
                }

            }
            // ============ ݸʽΪ end ==========================
            if (!empty($id_code_ary)) {
                foreach ($id_code_ary as $k=>$v) {
                    if (in_array($k . "_" . $v['inventory_id'], $have_set_ary)) {
                        $sql = "UPDATE `product_inventory` SET `qty` = '" . $v['qty'] . "', `special_price` ='" . $v['sp1'] . "', `special_price2` ='" . $v['sp2'] . "'  WHERE `product_id` = '$k' AND `inventory_id`= '" . $v['inventory_id'] . "';";			
                        $query = $db->query($sql);
                    } else {
                        $sql = "INSERT INTO `product_inventory` (`product_id`, `inventory_id`, `qty`, `special_price`, `special_price2`) VALUES ('$k', '" . $v['inventory_id'] . "', '" . $v['qty'] . "', '" . $v['sp1'] . "', '" . $v['sp2'] . "');";
                        $query = $db->query($sql);
                    }
                }
            }

            //
            $sql = "UPDATE `inventory` SET `update` = '$timestamp' WHERE `name` IN (" . $inventory_name_str . ")";
            $query = $db->query($sql);

        } else {
            // ============ ݸʽΪ start ========================
            foreach ($excel_ary as $k=>$v) {
                if ($k>=1 && isset($v[0]) && trim($v[0])!= '' && is_numeric($v[1]) && $v[1]>=0 && $v[3] != '') {
                    if (!isset($v[2])) {
                        $v[2] = 0;
                    }


                    $v[0] = strtolower($v[0]);
                    $v[2] = strtolower($v[2]) == 'nil' ? 0 : $v[2];
                    $id_ary[] = $v[0];
                    $code_ary[$v[0]]['code'] = $v[0];
                    $code_ary[$v[0]]['qty'] = $v[1];
                    $code_ary[$v[0]]['sp'] = $v[2];
                    $code_ary[$v[0]]['inventory_name'] = trim($v[3]);
                    $inventory_name_ary[] = trim($v[3]);
                }
            }

            //echo "<pre>";print_r($inventory_name_ary);exit;
            $id_ary = array_unique($id_ary);
            $inventory_name_ary = array_unique($inventory_name_ary);
            $inventory_name_str = implode("', '", $inventory_name_ary);
            $inventory_name_str = "'" . $inventory_name_str . "'";
            $sql = "SELECT `id`, `name` FROM `inventory`";
            $query = $db->query($sql);
            while($rs = $db->fetch_array($query)) {
                $inventory_name_id_ary[$rs['name']] = $rs['id'];
            }

            $id_code_ary = array();
            $have_set_ary = array();
            $code_tmp_ary = array();

            if (!empty($id_ary)) {
                $id_str = implode("', '", $id_ary);
                $id_str = "'" . $id_str . "'";
                $sql = "SELECT `id`, `code` FROM `product` WHERE `code` IN (" . $id_str . ")";
                $query = $db->query($sql);
                while($rs = $db->fetch_array($query)) {
                    $id_ary2[] = $rs['id'];
                    $rs['code'] = strtolower($rs['code']);
                    $code_tmp_ary[] = $rs['code'];   //дڵcode飬жЩcodeڵ
                    $id_code_ary[$rs['id']] = $code_ary[$rs['code']];
                    $id_code_ary[$rs['id']]['inventory_id'] = $inventory_name_id_ary[$code_ary[$rs['code']]['inventory_name']];
                }
                // жЩcode
                $no_code_ary = '';
                foreach ($id_ary as $k=>$v) {
                    if(!in_array($v, $code_tmp_ary)) {
                        $error_str .= $v . "<br>\r\n";
                    }
                }
                if (!empty($error_str)) {
                    $error_str = "<div class='js_msg'>code not exist:</div><br><br>" . $error_str;
                }

                if (!empty($id_ary2)) {
                    // жЩƷĳinventoryqtyѴ
                    $sql = "SELECT `product_id`, `inventory_id` FROM `product_inventory` WHERE `product_id` IN (" . implode(",", $id_ary2) . ")";

                    $query = $db->query($sql);
                    while($rs = $db->fetch_array($query)) {
                        $have_set_ary[] = $rs['product_id'] . "_" . $rs['inventory_id'];
                    }
                    $have_set_ary = array_unique($have_set_ary);
                }

            }
            // ============ ݸʽΪ end ==========================
            if (!empty($id_code_ary)) {
                foreach ($id_code_ary as $k=>$v) {
                    if (in_array($k . "_" . $v['inventory_id'], $have_set_ary)) {
                        $sql = "UPDATE `product_inventory` SET `qty` = '" . $v['qty'] . "', `special_price` ='" . $v['sp'] . "'  WHERE `product_id` = '$k' AND `inventory_id`= '" . $v['inventory_id'] . "'";
                        $query = $db->query($sql);
                    } else {
                        $sql = "INSERT INTO `product_inventory` (`product_id`, `inventory_id`, `qty`, `special_price`) VALUES ('$k', '" . $v['inventory_id'] . "', '" . $v['qty'] . "', '" . $v['sp'] . "')";	
                        $query = $db->query($sql);
                    }
		        }
            }
            //
            $sql = "UPDATE `inventory` SET `update` = '$timestamp' WHERE `name` IN (" . $inventory_name_str . ")";
            $query = $db->query($sql);

            // ============================= import inventory data end==========================
        }
    }




} elseif ($importtype == '3') {
    if(strtolower(getExt($_FILES['upload_file']['name'])) == "zip" && $_FILES['upload_file']['size'] > 0 && ($_FILES['upload_file']['type'] == "application/x-zip-compressed")){

        require_once '../include/zip.class.php';

        $upload_file = $_FILES['upload_file']['tmp_name'];
        $pic_file_name = "../attachments/" . $_FILES['upload_file']['name'];
        if (!move_uploaded_file($_FILES['upload_file']['tmp_name'], $pic_file_name)) {
            // ļʧ
            $error_str = 'upload zip file failed<br>';
        } else {
            $tmp_file2 = '../attachments/';
            $z=new Zip;
            $z->Extract($pic_file_name, $tmp_file2);
            @unlink($pic_file_name);
        }
    } elseif ($_FILES['upload_file']['size'] > 0) {
        $error_str .= "<div class='js_msg'>picture file error</div>";
    }
}


if ($upload_file != "" && $error_str == '') {
    $error_str = "<span class='red_font'>Import data Succeed</span>";
}

/*
$debug = 1;
debuginfo();
echo "<pre>";
print_r($db->sql_ary);
*/

include_once(JANSEN_ROOT . 'admin/menu_list.php');
require_once PrintEot('a_header');
require_once PrintEot('a_p_import_excel');
require_once PrintEot('a_footer');
?>
